﻿using Dapper;
using SassModule.Contracts;
using SassModule.Entity;
using SassModule.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SassModule.Services
{
    /// <summary>
    /// 用户服务
    /// </summary>
    public class UserService : IUserService
    {
        /// <summary>
        /// 添加账号
        /// </summary>
        /// <param name="info"></param>
        /// <param name="dbStr"></param>
        /// <returns></returns>
        public string Add(User info, string dbStr = null)
        {
            using var db = SassModule.ConnCache.GetConn();
            //先验证是否重复
            var exist= db.Query<string>("SELECT UserName FROM Sys_User WHERE UserName=@UserName", new { info.UserName }).ToList();
            if (exist.Count>0)
            {
                return "用户名已经存在";
            }
            //获取账套Id
            var sasses = db.Query<Sass>("SELECT * FROM Sys_Sass WHERE DB=@dbStr", new { dbStr }).ToList();
            if (sasses.Count <= 0) return "账套不存在";
            info.SassId = sasses[0].Id;
            info.Id = Guid.NewGuid();
            db.Execute("INSERT INTO dbo.Sys_User(Id,Name,Password,Email,Mobile,CreateTime,Description,Status,UserName,SassId) VALUES(NEWID(),@Name,@Password,@Email,@Mobile,GETDATE(),@Description,'NORMAL',@UserName,@SassId)", info);
            return null;
        }
        /// <summary>
        /// 根据用户Id删除用户
        /// </summary>
        /// <param name="id"></param>
        /// <param name="dbStr"></param>
        public void Del(Guid id, string dbStr = null)
        {
            using var db = SassModule.ConnCache.GetConn();
            db.Execute("DELETE FROM Sys_User WHERE Id=@id", new { id});
        }
        /// <summary>
        /// 获取用户拥有的角色列表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="dbStr"></param>
        /// <returns></returns>
        public IEnumerable<Guid> GetRoles(Guid id, string dbStr = null)
        {
            using var db = SassModule.ConnCache.GetConn();
            return db.Query<Guid>("SELECT RId FROM Sys_UserRole WHERE UId=@id", new { id });
        }

        /// <summary>
        /// 获取该账套下所有的用户
        /// </summary>
        /// <param name="dbStr"></param>
        /// <returns></returns>
        public IEnumerable<User> Index(string dbStr = null)
        {
            using var db = SassModule.ConnCache.GetConn();
            //获取账套Id
            var sasses = db.Query<Sass>("SELECT * FROM Sys_Sass WHERE DB=@dbStr", new { dbStr }).ToList();
            if (sasses.Count <= 0) return null;
            return db.Query<User>("SELECT * FROM SYS_USER WHERE SassId=@sassId",new { sassId=sasses[0].Id});
        }
        /// <summary>
        /// 根据Id获取用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="dbStr"></param>
        /// <returns></returns>
        public User Index(Guid id, string dbStr = null)
        {
            using var db = SassModule.ConnCache.GetConn();
            var sasses= db.Query<User>("SELECT Id,UserName,Name,DB FROM Sys_Sass WHERE Id=id", new { id}).ToList();
            if (sasses.Count > 0) return sasses[0];
            var users = db.Query<User>("SELECT u.Id,u.UserName,u.Name,s.DB FROM Sys_User U left join Sys_Sass s on u.SassId=s.Id WHERE u.Id=id",new { id}).ToList();
            if (users.Count > 0) return users[0];
            return null;
        }

        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public LoginSuccessInfo Login(TokenViewModel info)
        {
            using var db = SassModule.ConnCache.GetConn();
            var sasses= db.Query<Sass>("SELECT * FROM Sys_Sass WHERE UserName=@UserName AND Password=@Password AND Status='NORMAL'", info).ToList();
            LoginSuccessInfo response = null;
            if (sasses.Count>0)
            {
                response = new LoginSuccessInfo();
                response.Name = sasses[0].Name;
                response.UserName = sasses[0].UserName;
                response.Id= sasses[0].Id;
                response.Db= sasses[0].DB;
            }
            else
            {
                //查找用户表
                var  users = db.Query<User>("SELECT * FROM Sys_User WHERE UserName=@UserName AND Password=@Password",  info).ToList();
                if (users.Count>0)
                {
                    sasses=db.Query<Sass>("SELECT [Id],[Name],[UserName],[Password],[Mobile],[Email],[Des],[AllowUsers],[AllowMachines],[CreateTime],[Description],[Status],[DB],[CompanyName],[TaxNum],[Contact],[Bank],[BankNum],[Remark],[Expire] FROM [dbo].[Sys_Sass] WHERE Id=@sassId AND Status='NORMAL'", new { sassId = users[0].SassId }).ToList();
                    if (sasses.Count>0)
                    {
                        response = new LoginSuccessInfo();
                        response.Name = users[0].Name;
                        response.UserName = users[0].UserName;
                        response.Id = users[0].Id;
                        response.Db = sasses[0].DB;
                    }
                }
            }
            if (response!=null)
            {
                //获取title和img
                var datas= db.Query<LoginSuccessInfo>("SELECT Title,Img FROM Sys_Sass WHERE Id=@id", new { id = response.Id }).ToList();
                if (datas.Count>0)
                {
                    response.Img = datas[0].Img;
                    response.Title = datas[0].Title;
                }
                return response;
            }
            return new LoginSuccessInfo() {Msg="用户名或密码不正确" };
        }
        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="user"></param>
        /// <param name="dbStr"></param>
        public void Reset(UserPasswordViewModel user,string dbStr = null)
        {
            using var db = SassModule.ConnCache.GetConn();
            db.Execute("UPDATE Sys_User SET Password=@Password WHERE Id=@Id", user);
        }
        /// <summary>
        /// 给用户设置角色
        /// </summary>
        /// <param name="id"></param>
        /// <param name="roles"></param>
        /// <param name="dbStr"></param>
        public void SetRoles(Guid id, List<Guid> roles, string dbStr = null)
        {
            using var db = SassModule.ConnCache.GetConn();
            db.Execute("DELETE FROM Sys_UserRole WHERE UId=@id", new { id });
            foreach (var item in roles)
            {
                db.Execute("INSERT INTO Sys_UserRole(UId,RId) VALUES (@uid,@rid)", new {uid= id, rid = item });
            }
        }
        /// <summary>
        /// 个人设置
        /// </summary>
        /// <param name="user"></param>
        /// <param name="dbStr"></param>
        public string Setting(UserSetting user, string dbStr = null)
        {
            using var db = SassModule.ConnCache.GetConn();
            var exists = db.Query<Guid>("SELECT [Id] FROM [dbo].[Sys_Sass] WHERE Id=@id AND Password=@password", new {id= user.Id, password =user.OldPassword}).ToList();
            if (exists.Count>0)
            {
                //说明是管理员
                db.Execute("UPDATE Sys_Sass SET Password=@password WHERE Id=@id", new { password=user.NewPassword,id=user.Id });
            }
            else
            {
                exists = db.Query<Guid>("SELECT Id FROM Sys_User WHERE Id=@id And Password=@password", new { id = user.Id, password = user.OldPassword }).ToList();
                if (exists.Count>0)
                {
                    db.Execute("UPDATE Sys_User SET Name=@name,Password=@password WHERE Id=@id", new { name = user.Name, password = user.NewPassword, id = user.Id });
                }
            }
            return "原密码错误!";
        }

        public void UpdateUser(User info)
        {
            if (info.Id == null) return;
            //写sql语句
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("UPDATE  Sys_User SET");
            if (info.Name !=null)
            {
                sql.AppendLine("[Name] = @Name,");
            }
            if (info.Password!=null)
            {
                sql.AppendLine("[Password] = @Password,");
            }
            if (info.Email!=null)
            {
                sql.AppendLine("[Email] = @Email,");
            }
            if (info.Mobile!=null)
            {
                sql.AppendLine("[Mobile] = @Mobile,");
            }
            if (info.Status!=null)
            {
                sql.AppendLine("[Status] = @Status,");
            }
            if (info.Description!=null)
            {
                sql.AppendLine("[Description] = @Description,");
            }
            if (info.UserName!=null)
            {
                sql.AppendLine("[UserName] = @UserName,");
            }
            sql.AppendLine("[SassId] = @SassId");
            sql.AppendLine("WHERE [id]=@id");
            using var db = SassModule.ConnCache.GetConn();
            db.Execute(sql.ToString(),info);
        }
    }
}
